﻿using System;
using System.Configuration;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using KPIS.DBM;
using System.Data.SqlClient;
using System.Data.OleDb;
using KPIS.GERP.GBMS.MODEL;
using KPIS.GERP.GBMS.DAL;

namespace KPIS.GERP.GBMS.DAL
{
    public sealed class RequestExpensePlanDAL : IRequestExpensePlanDAL
    {
       
        IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

        public RequestExpensePlanDAL()
        {

        }

        public DataSet GetRequestExpensePlanByRequest(int reqSeq)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@seq", reqSeq);
                dbManager.AddParameters(1, "@status", SystemInfo._DeleteRecordStatus);

                strSQL = "SELECT S.BGM_SOURCE_NAME, EG.BGM_EXP_GRP_NAME, E.BGM_EXP_NAME, E1.BGM_EXP_NAME AS BGM_EXP_NAME_1"
                        + ", E1.BGM_EXP_DESCR, S.BGM_SOURCE_SEQ, E1.BGM_EXP_SEQ AS BGM_EXP_SEQ_2"
                        + ", S.BGM_SOURCE_CODE, EG.BGM_EXP_GRP_CODE, E.BGM_EXP_CODE, R.BGM_REQ_EXP_SEQ, R.BGM_REQ_EXP_DESCR"
                        + ", RP.BGM_REQ_EXP_PLAN_SEQ, COALESCE(R.BGM_REQ_EXP_QTY, 1) * R.BGM_REQ_EXP_PRICE AS BGM_REQ_EXP_TOTAL"
                        + ", RP.AMOUNT_MONTH1, RP.AMOUNT_MONTH2, RP.AMOUNT_MONTH3, RP.AMOUNT_MONTH4, RP.AMOUNT_MONTH5, RP.AMOUNT_MONTH6"
                        + ", RP.AMOUNT_MONTH7, RP.AMOUNT_MONTH8, RP.AMOUNT_MONTH9, RP.AMOUNT_MONTH10, RP.AMOUNT_MONTH11, RP.AMOUNT_MONTH12"
                    + " FROM BGM_REQUEST_EXPENSE R"
                        + " INNER JOIN BGM_REQUEST_EXPENSE_PLAN RP ON R.BGM_REQ_EXP_SEQ = RP.BGM_REQ_EXP_SEQ"
                        + " INNER JOIN BGM_MAS_EXPENSE E1 ON R.BGM_EXP_SEQ = E1.BGM_EXP_SEQ"
                        + " INNER JOIN BGM_MAS_EXPENSE E ON E.BGM_EXP_SEQ = E1.UPPER_BGM_EXP_SEQ"
                        + " INNER JOIN BGM_MAS_EXPENSE_GROUP EG ON E.BGM_EXP_GRP_SEQ = EG.BGM_EXP_GRP_SEQ"
                        + " INNER JOIN BGM_MAS_SOURCE S ON EG.BGM_SOURCE_SEQ = S.BGM_SOURCE_SEQ"
                    + " WHERE R.BGM_REQ_SEQ = @seq"
                        + " AND R.RECORD_STATUS not like @status";

                return dbManager.ExecuteDataSet(CommandType.Text, strSQL);
            }
            catch
            {
                return null;
                throw;
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
        }

        public RequestExpensePlanInfo GetRequestExpensePlanByReqExpSeq(int reqExpSeq)
        {
            string strSQL;
            RequestExpensePlanInfo reqExpPlan = null;

            strSQL = "SELECT BGM_REQ_EXP_PLAN_SEQ, BGM_REQ_EXP_SEQ"
                + ", AMOUNT_MONTH1, AMOUNT_MONTH2, AMOUNT_MONTH3"
                + ", AMOUNT_MONTH4, AMOUNT_MONTH5, AMOUNT_MONTH6"
                + ", AMOUNT_MONTH7, AMOUNT_MONTH8, AMOUNT_MONTH9"
                + ", AMOUNT_MONTH10, AMOUNT_MONTH11, AMOUNT_MONTH12"
                + " FROM BGM_REQUEST_EXPENSE_PLAN"
                + " WHERE BGM_REQ_EXP_SEQ = @seq";

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@seq", reqExpSeq);
                dbManager.AddParameters(1, "@status", SystemInfo._DeleteRecordStatus);

                IDataReader rdr = dbManager.ExecuteReader(CommandType.Text, strSQL);

                while (rdr.Read())
                {
                    //reqExpPlan = new RequestExpensePlanInfo(rdr.GetInt32(0), rdr.GetInt32(1), (rdr.IsDBNull(2)) ? null : (Nullable<double>)rdr.GetDouble(2), (rdr.IsDBNull(3)) ? null : (Nullable<double>)rdr.GetDouble(3), (rdr.IsDBNull(4)) ? null : (Nullable<double>)rdr.GetDouble(4), (rdr.IsDBNull(5)) ? null : (Nullable<double>)rdr.GetDouble(5), (rdr.IsDBNull(6)) ? null : (Nullable<double>)rdr.GetDouble(6), (rdr.IsDBNull(7)) ? null : (Nullable<double>)rdr.GetDouble(7), (rdr.IsDBNull(8)) ? null : (Nullable<double>)rdr.GetDouble(8), (rdr.IsDBNull(9)) ? null : (Nullable<double>)rdr.GetDouble(9), (rdr.IsDBNull(10)) ? null : (Nullable<double>)rdr.GetDouble(10), (rdr.IsDBNull(11)) ? null : (Nullable<double>)rdr.GetDouble(11), (rdr.IsDBNull(12)) ? null : (Nullable<double>)rdr.GetDouble(12), (rdr.IsDBNull(13)) ? null : (Nullable<double>)rdr.GetDouble(13));
                }

                return reqExpPlan;
            }
            catch
            {
                return null;
                throw;
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
        }

        //public IList<RequestExpenseInfo> GetRequestExpenseByReqSeq(int reqSeq)
        //{
        //    string strSQL;

        //    List<RequestExpenseInfo> reqExpList = new List<RequestExpenseInfo>();

        //    try
        //    {
        //        dbManager.Open();
        //        dbManager.CreateParameters(1);

        //        dbManager.AddParameters(0, "@seq", reqSeq);
        //        dbManager.AddParameters(1, "@status", SystemInfo._DeleteRecordStatus);
                
        //        strSQL = "SELECT S.BGM_SOURCE_NAME, EG.BGM_EXP_GRP_NAME, E.BGM_EXP_NAME, E1.BGM_EXP_NAME AS BGM_EXP_NAME_1"
        //                + ", E1.BGM_EXP_DESCR, S.BGM_SOURCE_SEQ, E1.BGM_EXP_SEQ AS BGM_EXP_SEQ_2"
        //                + ", S.BGM_SOURCE_CODE, EG.BGM_EXP_GRP_CODE, E.BGM_EXP_CODE, R.BGM_REQ_EXP_SEQ"
        //                + ", R.BGM_REQ_EXP_DESCR, (COALESCE((R.BGM_REQ_EXP_QTY * R.BGM_REQ_EXP_PRICE), R.BGM_REQ_EXP_PRICE)) AS BGM_REQ_EXP_TOTAL"
        //                + ", R.BGM_REQ_EXP_QTY, R.ICM_RESOURCE_LIST_SEQ, BGM_REQ_EXP_MTEF1, BGM_REQ_EXP_MTEF2, BGM_REQ_EXP_MTEF3"
        //            + " FROM BGM_REQUEST_EXPENSE R"
        //                + " INNER JOIN BGM_MAS_EXPENSE E1 ON R.BGM_EXP_SEQ = E1.BGM_EXP_SEQ"
        //                + " INNER JOIN BGM_MAS_EXPENSE E ON E.BGM_EXP_SEQ = E1.UPPER_BGM_EXP_SEQ"
        //                + " INNER JOIN BGM_MAS_EXPENSE_GROUP EG ON E.BGM_EXP_GRP_SEQ = EG.BGM_EXP_GRP_SEQ"
        //                + " INNER JOIN BGM_MAS_SOURCE S ON EG.BGM_SOURCE_SEQ = S.BGM_SOURCE_SEQ"
        //            + " WHERE R.BGM_REQ_SEQ = @seq"
        //                + " AND R.RECORD_STATUS not like @status";

        //        IDataReader rdr = dbManager.ExecuteReader(CommandType.Text, strSQL);
                
        //        while (rdr.Read())
        //        {
        //            RequestExpenseInfo reqExp = new RequestExpenseInfo();

        //            //reqExp.ReqExpExpense = rdr.GetString(0);
        //            //reqExp.ReqExpDescr = rdr.GetString(1);
        //            //reqExp.ReqExpPrice = rdr.GetString(2);
        //            //reqExp.ReqExpQty = rdr.GetDateTime(3);
        //            //reqExp.ReqReason = rdr.GetString(4);
        //            //reqExp.ReqObject = rdr.GetString(5);
        //            //reqExp.ReqTarget = rdr.GetString(6);
        //            //reqExp.ReqPlan = rdr.GetString(7);
        //            //reqExp.ReqRemark = rdr.GetString(8);
        //            //reqExp.ReqStrategy = rdr.GetString(9);
        //            //reqExp.ReqJobSite = rdr.GetString(10);

        //            reqExpList.Add(reqExp);
        //        }

        //        return reqExpList;
        //    }
        //    catch
        //    {
        //        return null;
        //        throw;
        //    }
        //    finally
        //    {
        //        dbManager.CloseReader();
        //        dbManager.Dispose();
        //    }
        //}

        public IList<RequestInfo> GetRequestByJobSite()
        {
            List<RequestInfo> requestInfo = new List<RequestInfo>();

            //try
            //{
            //    dbManager.Open();

            //    IDataReader rdr = dbManager.ExecuteReader(CommandType.Text, "SELECT BGM_REQ_SEQ, BGM_REQ_CODE, BGM_REQ_NAME, BGM_REQ_DESCR, BGM_REQ_DATE, BGM_REQ_REASON, BGM_REQ_OBJECT, BGM_REQ_TARGET, BGM_REQ_PLAN, BGM_REQ_REMARK, BGM_STRATEGY_SEQ, JOB_SITE_SEQ, BGM_YEAR, RECORD_STATUS FROM BGM_REQUEST RQ");

            //    while (rdr.Read())
            //    {
            //        RequestInfo req = new RequestInfo(rdr.GetInt32(0), rdr.GetString(1), rdr.GetString(2), rdr.GetString(3), rdr.GetDateTime(4), null, rdr.GetString(5), rdr.GetString(6), rdr.GetInt32(7), rdr.GetDouble(8), rdr.GetString(9));
            //        requestInfo.Add(req);
            //    }

                return requestInfo;
            //}
            //catch
            //{
            //    return null;
            //    throw;
            //}
            //finally
            //{
            //    dbManager.CloseReader();
            //    dbManager.Dispose();
            //}
        }

       
        public int InsertExpense(int reqSeq, int exp, int user)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@reqSeq", reqSeq);
                dbManager.AddParameters(1, "@exp", exp);
                dbManager.AddParameters(2, "@status", SystemInfo._WorkRecordStatus);
                dbManager.AddParameters(3, "@user", user);


                strSQL = "INSERT INTO BGM_REQUEST_EXPENSE(BGM_REQ_SEQ, BGM_EXP_SEQ, BGM_SOURCE_SEQ, RECORD_STATUS, CREATED_BY)"
                    + " VALUES(@reqSeq, @exp, (select eg.bgm_source_seq from bgm_mas_expense e inner join bgm_mas_expense_group eg on e.bgm_exp_grp_seq = eg.bgm_exp_grp_seq where e.bgm_exp_seq = @exp), @status, @user)";

                return dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                return 0;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public int UpdateExpense(int reqExpSeq, string reqExpDescr, double reqExpPrice, int reqExpQty
        , double reqExpMtef1, double reqExpMtef2, double reqExpMtef3, string reqExpResource)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(9);

                dbManager.AddParameters(0, "@reqExpSeq", reqExpSeq);
                dbManager.AddParameters(1, "@reqExpDescr", reqExpDescr);
                dbManager.AddParameters(2, "@reqExpPrice", reqExpPrice);
                dbManager.AddParameters(3, "@reqExpQty", reqExpQty);
                dbManager.AddParameters(4, "@reqExpMtef1", reqExpMtef1);
                dbManager.AddParameters(5, "@reqExpMtef2", reqExpMtef2);
                dbManager.AddParameters(6, "@reqExpMtef3", reqExpMtef3);
                dbManager.AddParameters(7, "@reqExpResource", reqExpResource);
                dbManager.AddParameters(8, "@status", SystemInfo._WorkRecordStatus);

                strSQL = "UPDATE BGM_REQUEST_EXPENSE"
                    + " SET BGM_REQ_EXP_DESCR = @reqExpDescr"
                        + ", BGM_REQ_EXP_PRICE = @reqExpPrice"
                        + ", BGM_REQ_EXP_QTY = @reqExpQty"
                        + ", BGM_REQ_EXP_MTEF1 = @reqExpMtef1"
                        + ", BGM_REQ_EXP_MTEF2 = @reqExpMtef2"
                        + ", BGM_REQ_EXP_MTEF3 = @reqExpMtef3"
                        + ", ICM_RESOURCE_LIST_SEQ = @reqExpResource"
                        + ", RECORD_STATUS = @status"
                        + ", UPDATED_WHEn = GetDate()"
                    + " WHERE BGM_REQ_EXP_SEQ = @reqExpSeq";

                return dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                return 0;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public int DeleteExpense(int seq, int updated_by)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@seq", seq);
                dbManager.AddParameters(1, "@record_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(2, "@updated_by", updated_by);

                strSQL = "UPDATE BGM_REQUEST_EXPENSE"
                    + " SET RECORD_STATUS = @record_status"
                        + ", UPDATED_BY = @updated_by"
                        + ", UPDATED_WHEN = GetDate()"
                    + " WHERE BGM_REQ_EXP_SEQ = @seq";

                return dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                return 0;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        //--------------------------------- BGM_Request_Expense_Plan --------------------------------

        public DataSet LoadReqExpPlanGrid(int seq)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@seq", seq);
                dbManager.AddParameters(1, "@status", SystemInfo._DeleteRecordStatus);

                strSQL = "SELECT S.BGM_SOURCE_NAME, EG.BGM_EXP_GRP_NAME, E.BGM_EXP_NAME, E1.BGM_EXP_NAME AS BGM_EXP_NAME_1"
                        + ", E1.BGM_EXP_DESCR, S.BGM_SOURCE_SEQ, E1.BGM_EXP_SEQ AS BGM_EXP_SEQ_2"
                        + ", S.BGM_SOURCE_CODE, EG.BGM_EXP_GRP_CODE, E.BGM_EXP_CODE, R.BGM_REQ_EXP_SEQ"
                        + ", R.BGM_REQ_EXP_DESCR, (COALESCE(R.BGM_REQ_EXP_QTY, 1) * R.BGM_REQ_EXP_PRICE) AS BGM_REQ_EXP_TOTAL"
                        + ", RP.AMOUNT_MONTH1, RP.AMOUNT_MONTH2, RP.AMOUNT_MONTH3, RP.AMOUNT_MONTH4, RP.AMOUNT_MONTH5"
                        + ", RP.AMOUNT_MONTH6, RP.AMOUNT_MONTH7, RP.AMOUNT_MONTH8, RP.AMOUNT_MONTH9, RP.AMOUNT_MONTH10"
                        + ", RP.AMOUNT_MONTH11, RP.AMOUNT_MONTH12, RP.BGM_REQ_EXP_PLAN_SEQ"                        
                    + " FROM BGM_REQUEST_EXPENSE R"
                        + " INNER JOIN BGM_REQUEST_EXPENSE_PLAN RP ON R.BGM_REQ_EXP_SEQ = RP.BGM_REQ_EXP_SEQ"
                        + " INNER JOIN BGM_MAS_EXPENSE E1 ON R.BGM_EXP_SEQ = E1.BGM_EXP_SEQ"
                        + " INNER JOIN BGM_MAS_EXPENSE E ON E.BGM_EXP_SEQ = E1.UPPER_BGM_EXP_SEQ"
                        + " INNER JOIN BGM_MAS_EXPENSE_GROUP EG ON E.BGM_EXP_GRP_SEQ = EG.BGM_EXP_GRP_SEQ"
                        + " INNER JOIN BGM_MAS_SOURCE S ON EG.BGM_SOURCE_SEQ = S.BGM_SOURCE_SEQ"
                    + " WHERE R.BGM_REQ_SEQ = @seq"
                        + " AND R.RECORD_STATUS not like @status";

                return dbManager.ExecuteDataSet(CommandType.Text, strSQL);
            }
            catch
            {
                return null;
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }
    }
}
